package com.mlamp.demo.utils;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/*
 * Excel导入信息类
 */
public class ExcelImportUtil {
	
	public static void main(String[] args) throws Exception {
		File fileExcel=new File("D:\\cvs\\1.xlsx");
		Map<String, Object> map=new ExcelImportUtil().importExcel(fileExcel);
		System.out.println(map);
	}

	// 取得logger日志对象
	private static Logger logger = Logger.getLogger(ExcelImportUtil.class);
	// 设置表头所占用的行数
	public final static int HEADER_NUM = 2;
	
	
	/**
	 * 导入 Excel 文件为对象集合
	 * 
	 * @param fileExcel
	 *            :待解析的 Excel 文件
	 * @return result
	 * @throws Exception
	 */
	@SuppressWarnings("null")
	public Map<String, Object> importExcel(File fileExcel) throws Exception {
		Map<String, Object> result = new HashMap<String, Object>();
		// 判断Excel文件存在性
		if (null != fileExcel || fileExcel.isFile() == true) {
			// 定义文本薄
			XSSFWorkbook workbook = null;
			try {
				// 取得文本薄对象
				workbook = new XSSFWorkbook(new FileInputStream(fileExcel));
				// 调用解析方法生成集合对象
				result = excelResolution(workbook);
			} catch (Exception e) {
				logger.error("解析excel失败");
			}
		}
		return result;
	}

	/*
	 * 解析Excel文件 参数：contactBean关联数据库对象Bean fileExcel解析对应的Excel文件路径
	 */
	@SuppressWarnings({ "unused" })
	private Map<String, Object> excelResolution(Workbook workbook) throws Exception {
		// 取得sheet1对象
		Sheet sheet1 = workbook.getSheetAt(0);
		// 创建sheet的Map对象实例,按顺序组装字段名称
		Map<String, Object> mapColums = new HashMap<String, Object>();
		Map<String, Object> mapNames = new HashMap<String, Object>();
		Map<String, Object> mapReturnItem = new HashMap<String, Object>();
		// 创建必填项验证实例
		Map<String, String> mapYanzheng = new HashMap<String, String>();
		// 创建索引和数据集合对象
		List<Map<String, Object>> mapDatasList = new ArrayList<Map<String, Object>>();
		// 创建索引和数据集合对象
		List<Map<String, Object>> mapResultList = new ArrayList<Map<String, Object>>();
		// 创建索引和数据验证对象
		List<Map<String, String>> mapErrorMsgList = new ArrayList<Map<String, String>>();
		// 包含验证信息的集合
		Map<String, Object> mapEnd = new HashMap<String, Object>();
		// 定义字段索引
		int countColun = 0;
		int countName = 0;
		// 定义数据索引
		int countData = 0;

		// 迭代sheet的Row 0取得数据库对应字段和索引集合对象,表头
		for(Cell cell : sheet1.getRow(0)){
			if(sheet1.getRow(0).getCell(countColun) != null && !"".equals(sheet1.getRow(0).getCell(countColun))){
				mapColums.put(String.valueOf(countColun), sheet1.getRow(0).getCell(countColun).toString());
				countColun++;
			}
		}	
		
		// 迭代sheet的Row 1取得数据库对应字段名和索引集合对象
		for(Cell cell : sheet1.getRow(1)){
			if(sheet1.getRow(1).getCell(countName) != null && !"".equals(sheet1.getRow(1).getCell(countName))){
				mapNames.put(String.valueOf(countName), sheet1.getRow(1).getCell(countName).toString());
				countName++;
			}
		}	
		
		// 迭代sheet的Row取得数据和索引集合对象
		for (int rowNum = 0; rowNum <= sheet1.getLastRowNum(); rowNum++) {
			// 跳过前2行
			if (rowNum < 2) {
				continue;
			}
			// 将属性名和字段名存入Map集合中
			Map<String, Object> mapInfos = new HashMap<String, Object>();
			Row xRow = sheet1.getRow(rowNum);
			if (xRow == null) {
				continue;
			}
			// 循环cell
			for(int cellNum = 0; cellNum <= xRow.getLastCellNum(); cellNum++){
				Cell xCell = xRow.getCell(cellNum);
				if (xCell == null) {
					continue;
				}
				mapInfos.put(String.valueOf(cellNum), getValue(xCell));
			}
			mapDatasList.add(mapInfos);
		}
		
		for(int i = 0; i < mapDatasList.size(); i++){
			boolean flag = false;
			Map<String, String> mapError = new HashMap<String, String>();
			Map<String, Object> mapResult = new HashMap<String, Object>();
			for (String key : mapColums.keySet()) {
				// 将索引关联存入Map集合中
				if (mapColums.get(key) == null) {
					break;
				} else {
					// 判断是否为必填项
					if("notnull".equals(getSplitValueRight(mapColums.get(key).toString()))){
						// 必填项为空
						if (mapDatasList.get(i).get(key) == null || "".equals(mapDatasList.get(i).get(key))) {
							mapError.put(mapNames.get(key).toString(), "第"+String.valueOf(i+1)+"行"+mapNames.get(key)+"为空,请重新输入");
							flag = true;
						}
					}
					if (mapDatasList.get(i).get(key) == null || "".equals(mapDatasList.get(i).get(key))) {
						mapResult.put(mapColums.get(key).toString(),"");
					} else {
						mapResult.put(getSplitValueLeft(mapColums.get(key).toString()), mapDatasList.get(i).get(key).toString());
					}
				}
			}
			if (flag == true) {
				mapErrorMsgList.add(mapError);
			} else {
				mapResultList.add(mapResult);
			}
		}
		mapReturnItem.put("error", mapErrorMsgList);
		mapReturnItem.put("success", mapResultList);
		
	    return mapReturnItem;
	}
	
	private String getValue(Cell xCell) {  
        if (xCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {  
            return String.valueOf(xCell.getBooleanCellValue());  
        } else if (xCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {  
            return String.valueOf(xCell.getNumericCellValue());  
        } else {
            return String.valueOf(xCell.getStringCellValue());  
        }
    }

	

	/**
	 * 获得当前系统时间
	 * 
	 * @return dateSte
	 */
	@SuppressWarnings("unused")
	private String getDate() {
		Date dateVal = Calendar.getInstance().getTime();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateSte = sdf.format(dateVal);
		return dateSte;
	}
	
	private String getSplitValueLeft(String cellValue){
		String splitValue = null;
		StringBuffer sb = new StringBuffer();
		if (cellValue != null) {
			int countLeft = cellValue.indexOf(",");
			if (countLeft > 0) {
				sb.append(cellValue.substring(0,countLeft));
				splitValue = sb.toString();
			} else {
				splitValue = cellValue;
			}
		}
		
		return splitValue;
	}
	
	private String getSplitValueRight(String cellValue){
		String splitValue = null;
		StringBuffer sb = new StringBuffer();
		if (cellValue != null) {
			int countRight = cellValue.indexOf(",");
			if (countRight > 0) {
				sb.append(cellValue.substring(countRight+1,cellValue.length()));
				splitValue = sb.toString();
			} else {
				splitValue = cellValue;
			}
		}
		return splitValue;
	}
}